const mysql = require('mysql');
const config = require('../db_config');
let { DB_HOST, DB_NAME, DB_PASS, DB_PORT, DB_USER } = config;
const db = mysql.createPool({
  user: DB_USER,
  password: DB_PASS,
  host: DB_HOST,
  port: DB_PORT,
  database: DB_NAME
});
//过滤字符串
function filterString(val) {
  if (typeof val == 'string') {
    return val.replace(/'/, "\\'");
  } else {
    return val;
  }
}
//重写query
db._query = db.query;
db.query = function(sql) {
  return new Promise((resolve, reject) => {
    db._query(sql, (err, data) => {
      err && reject(err);
      resolve(data);
    });
  }).catch(e => {
    console.log(e);
  });
};
//update
db.update = function(table, symbol, data) {
  let arr = [];
  for (let key in data) {
    if (typeof data[key] === 'number') {
      arr.push(`${key}=${data[key]}`);
    } else {
      arr.push(`${key}='${data[key]}'`);
    }
  }
  let str = [];
  for (let key in symbol) {
    str.push(`${key}='${symbol[key]}'`);
  }
  console.log(arr.join(','), str.join(' AND '));
  return db.query(
    `UPDATE ${table} SET ${arr.join(',')} WHERE ${str.join(' AND ')} `
  );
};
//insert
db.insert = function(table, data) {
  let keys = [];
  let values = [];
  for (let key in data) {
    keys.push(key);
    if (typeof data[key] === 'string') {
      values.push("'" + filterString(data[key].toString()) + "'");
    } else if (typeof data[key] === 'number') {
      values.push(data[key]);
    }
  }
  return db.query(
    `INSERT INTO ${table} (${keys.join(',')}) VALUES(${values.join(',')}) `
  );
};
//select
db.select = function(table, fields, where_json) {
  let where = '';
  !fields && (fields = '*');
  if (!where_json) {
    where = '1=1';
  } else {
    let keys = [];
    for (let key in where_json) {
      keys.push(key);
    }
    where = keys
      .map(key => {
        return ` ${key}='${filterString(where_json[key])}'`;
      })
      .join(' AND ');
  }
  return db.query(`SELECT ${fields} FROM ${table} WHERE ${where}`);
};
db.delete = function(table, where_json) {
  assert(where_json);
  let arr = [];
  for (let key in where_json) {
    arr.push(`${key}='${filterString(where_json[key])}'`);
  }
  return db.query(`DELETE FROM ${table} WHERE ${arr.join(' AND ')}`);
};

module.exports = db;
